﻿using Oracle.DataAccess.Client;
using System;
using Oracle.DataAccess.Types;
using System.Data;
using System.Text;
using System.IO;
using System.Collections.Generic;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Text.RegularExpressions;
using System.Collections;


namespace Com.FoxGlobal.Daniel.Midea.TemplateService
{

    class DBAgent
    {
        OracleConnection objConnection = null;
        OracleCommand objCommand = null;

        private int _query_prefix = -1;
        private static int _prefix_number = 100;
        private static Object _lock_object = new object();

        /// <summary>
        /// 获取连接字段串
        /// </summary>
        /// <param name="Section"></param>
        /// <returns></returns>
        public static string GetConnectionString(String Section = "ODS.Oracle")
        {
            return ConfigurationManager.ConnectionStrings[Section].ToString();
        }

        /// <summary>
        /// 获取数据库Package包名
        /// </summary>
        public string PackageName
        {
            get { return ConfigurationManager.AppSettings["ODS:PackageName"].ToString(); }
        }

        /// <summary>
        /// 更新前辍
        /// </summary>
        public string TablePrefix
        {
            get
            {
                if (_query_prefix < 0)
                {
                    lock (System.Web.HttpRuntime.Cache)
                    {
                        if (System.Web.HttpRuntime.Cache["PrefixNumber"] != null)
                        {
                            try
                            {
                                _prefix_number = int.Parse(System.Web.HttpRuntime.Cache["PrefixNumber"].ToString());
                            }
                            catch
                            {
                                _prefix_number = new Random().Next(500, 1000);
                            }
                        }
                        else
                        {
                            _prefix_number = new Random().Next(1, 500);
                        }

                        _query_prefix = _prefix_number;
                        _prefix_number = (_prefix_number + 1) % 1000;

                        System.Web.HttpRuntime.Cache["PrefixNumber"] = _prefix_number;
                    }
                }
                return _query_prefix.ToString();
            }
        }

     
        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="Section"></param>
        public void Connect(String Section="ODS.Oracle")
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "Connect to database {0}", Section);
            try
            {
                if (objConnection == null || objConnection.State != ConnectionState.Open)
                {
                    objConnection = new OracleConnection(GetConnectionString(Section));
                    objConnection.Open();
                }
                else
                {
                    Logging.WriteLog(LoggingLevel.DEBUG, "Connection already started!");
                }
            }
            catch (Exception ex)
            {
                Logging.WriteException(ex, "Error occur while connect to database: {0}", GetConnectionString(Section));
                objConnection = null;
                throw ex;
            }
        }

        /// <summary>
        /// 断开数据库连接
        /// </summary>
        public void Disconnct()
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "Disconnect from database");
            try
            {
                if (objConnection != null && objConnection.State == ConnectionState.Open)
                {
                    objConnection.Close();
                }
                else
                {
                     Logging.WriteLog(LoggingLevel.DEBUG, "No active connection before.");
                }
                objConnection = null;
            }
            catch (Exception ex)
            {
                Logging.WriteException(ex, "Error while disconnected");
            }
        }

        /// <summary>
        /// 更新前创建临时表
        /// </summary>
        /// <param name="table">更新的目标表名称</param>
        public void BeforeUpdate(string table)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "BeforeUpdate ({0})", table);

            objCommand = new OracleCommand();
            objCommand.Connection = objConnection;
            objCommand.CommandText = String.Format("{0}.BeforeUpdate", PackageName);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.Add("sPrefix", OracleDbType.Varchar2, 40).Value = TablePrefix;
            objCommand.Parameters.Add("sTableName", OracleDbType.Varchar2, 40).Value = table;
            objCommand.ExecuteNonQuery();
        }


        /// <summary>
        /// 查询前处理
        /// </summary>
        /// <param name="table">查询的目标表</param>
        public void BeforeQuery(string table)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "BeforeQuery ({0})", table);

            objCommand = new OracleCommand();
            objCommand.Connection = objConnection;
            objCommand.CommandText = String.Format("{0}.BeforeQuery", PackageName);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.Add("sPrefix", OracleDbType.Varchar2, 40).Value = TablePrefix;
            objCommand.Parameters.Add("sTableName", OracleDbType.Varchar2, 40).Value = table;
            objCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// Description:  
        /// Calls:            
        /// Called By:     
        /// Parameter:   
        /// Others:         
        /// </summary>
        /// <returns></returns>
        public void ProcessData(string table, string view, string comparsion, string columns, string keyset, string povdefine)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "ProcessData(table='{0}', view='{1}', comparsion='{2}', columns='{3}', keyset='{4}', povdefine='{5}')",
                                        table, view, comparsion, columns, keyset, povdefine);

            objCommand = new OracleCommand();
            objCommand.Connection = objConnection;
            objCommand.CommandText = String.Format("{0}.ProcessData", PackageName);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.Add("sPrefix", OracleDbType.Varchar2, 40).Value = TablePrefix;
            objCommand.Parameters.Add("sTableName", OracleDbType.Varchar2, 200).Value = table;
            objCommand.Parameters.Add("sViewName", OracleDbType.Varchar2, 200).Value = view;
            objCommand.Parameters.Add("sCompasion", OracleDbType.Varchar2, 400).Value = comparsion;
            objCommand.Parameters.Add("sColumns", OracleDbType.Varchar2, 400).Value = columns;
            objCommand.Parameters.Add("sKeySet", OracleDbType.Varchar2, 400).Value = keyset;
            objCommand.Parameters.Add("sPOVDefine", OracleDbType.Varchar2, 400).Value = povdefine;
            objCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        public void MoveHistory(string table)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "MoveHistory({0})", table);

            objCommand = new OracleCommand();
            objCommand.Connection = objConnection;
            objCommand.CommandText = String.Format("{0}.MoveHistory", PackageName); 
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.Add("sPrefix", OracleDbType.Varchar2, 40).Value = TablePrefix;
            objCommand.Parameters.Add("sTableName", OracleDbType.Varchar2, 200).Value = table;
            objCommand.ExecuteNonQuery();

        }

        /// <summary>
        /// 查询数据到临时表
        /// </summary>
        /// <param name="table">查询数据源表</param>
        /// <param name="comparsion">查询条件</param>
        /// <param name="fieldname">查询的数据字段</param>
        /// <param name="pov">查询所使用的POV</param>
        public void QueryData(string table, string comparsion, string fieldname, string pov)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "QueryData(table='{0}', comparsion='{1}', keyset='{2}', pov='{3}'",
                                        table, comparsion, fieldname, pov);

            objCommand = new OracleCommand();

            objCommand.Connection = objConnection;
            objCommand.CommandText = String.Format("{0}.QueryData", PackageName); 
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.Add("sPrefix", OracleDbType.Varchar2, 40).Value = TablePrefix;
            objCommand.Parameters.Add("sTableName", OracleDbType.Varchar2, 200).Value = table;
            objCommand.Parameters.Add("sCompasion", OracleDbType.Varchar2, 400).Value = comparsion;
            objCommand.Parameters.Add("sFieldName", OracleDbType.Varchar2, 400).Value = fieldname;
            objCommand.Parameters.Add("sPOVDefine", OracleDbType.Varchar2, 400).Value = pov;

            objCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 提交结束后清理临时表
        /// </summary>
        /// <param name="table">提交目标表</param>
        public void AfterUpdate(string table)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "AfterUpdate({0})", table);

            objCommand = new OracleCommand();
            objCommand.Connection = objConnection;
            objCommand.CommandText = String.Format("{0}.AfterUpdate", PackageName);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.Add("sPrefix", OracleDbType.Varchar2, 40).Value = TablePrefix;
            objCommand.Parameters.Add("sTableName", OracleDbType.Varchar2, 40).Value = table;
            objCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 查询结束后清理临时表
        /// </summary>
        /// <param name="table">查询目标表信息</param>
        public void AfterQuery(string table)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "AfterQuery({0})", table);

            objCommand = new OracleCommand();

            objCommand.Connection = objConnection;
            objCommand.CommandText = String.Format("{0}.AfterQuery", PackageName); 
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.Add("sPrefix", OracleDbType.Varchar2, 40).Value = TablePrefix;
            objCommand.Parameters.Add("sTableName", OracleDbType.Varchar2, 40).Value = table;

            objCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 获取查询结果
        /// </summary>
        /// <param name="table">查询临时表</param>
        /// <returns></returns>
        public string GetQueryResult(string table)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "GetQueryResult({0})", table);

            objCommand = new OracleCommand();
            objCommand.Connection = objConnection;
            objCommand.CommandText = String.Format("select ColumnIDX, RowIDX, FieldValue from {0}{1}", table, TablePrefix);
            Logging.WriteLog(LoggingLevel.DEBUG, "Sql = {0}", objCommand.CommandText);
         
            List<String> results = new List<String>();
            OracleDataReader reader = objCommand.ExecuteReader();

            Logging.WriteLog(LoggingLevel.DEBUG, "Records = {0}", reader.HasRows);

            while (reader.Read())
            {
                results.Add(String.Format("{0},{1},{2}", reader["ColumnIDX"], reader["RowIDX"], reader["FieldValue"].ToString().Trim()));
            }

            Logging.WriteLog(LoggingLevel.DEBUG, "RecordsCount = {0}", results.Count);
            return String.Join(";", results);
        }



        /// <summary>
        /// 获取查询结果
        /// </summary>
        /// <param name="table">查询临时表</param>
        /// <returns></returns>
        public void ExecCommand(string sql, string scenario, string entity, string year, string period)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "ExecQuery({0})", sql);

            objCommand = new OracleCommand();
            objCommand.Connection = objConnection;
            objCommand.CommandText = sql;
            Logging.WriteLog(LoggingLevel.DEBUG, "Sql = {0}", objCommand.CommandText);

            objCommand.Parameters.Add("Scenario", OracleDbType.Varchar2, 40).Value = scenario;
            objCommand.Parameters.Add("Entity", OracleDbType.Varchar2, 40).Value = entity;
            objCommand.Parameters.Add("Year", OracleDbType.Varchar2, 40).Value = year;
            objCommand.Parameters.Add("Period", OracleDbType.Varchar2, 40).Value = period;

            objCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 获取列表信息
        /// </summary>
        /// <param name="table">数据查询表/视图</param>
        /// <param name="pov">POV信息</param>
        /// <param name="listfields">列表字段</param>
        /// <param name="columnindexs">列清单</param>
        /// <param name="rowindexs">行清单</param>
        /// <returns></returns>
        public string GetListResult(string table, string pov, string listfields,List<string> columnindexs, List<string> rowindexs)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "GetListResult(table='{0}', pov='{1}', listfields='{2}', rowindex='{3}', colindexs='{4}')", table, pov, listfields, string.Join(",", rowindexs), string.Join(",", columnindexs));

            objCommand = new OracleCommand();
            objCommand.Connection = objConnection;
            objCommand.CommandText = String.Format("select distinct {0} from {1} B where 1=1 {2} order by 1 ", listfields, table, pov);
            Logging.WriteLog(LoggingLevel.DEBUG, "Sql = {0}", objCommand.CommandText);

            List<String> results = new List<String>();
            OracleDataReader reader = objCommand.ExecuteReader();

            Logging.WriteLog(LoggingLevel.DEBUG, "Records = {0}", reader.HasRows);

            foreach( string rowindex in rowindexs)
            {
                if (reader.Read())
                {
                    for (int colindex = 0; colindex < columnindexs.Count; ++colindex)
                    {
                        results.Add(String.Format("{0},{1},{2}", columnindexs[colindex], rowindex, reader[colindex].ToString()));
                    }
                }
                else
                {
                    for (int colindex = 0; colindex < columnindexs.Count; ++colindex)
                    {
                        results.Add(String.Format("{0},{1},{2}", columnindexs[colindex], rowindex, ""));
                    }
                }
            }

            Logging.WriteLog(LoggingLevel.DEBUG, "RecordsCount = {0}", results.Count);
            return String.Join(";", results);
        }

        /// <summary>
        /// 将提交的数据批量插入提交临时表
        /// 查询临时表规划:    提交目标表<随机编号1~999>,  如hfm_target_input的临时表可能为 hfm_target_input99
        /// </summary>
        /// <param name="table">查询的数据表</param>
        /// <param name="keyColumns">维度字段</param>
        /// <param name="dataColumns">数据字段</param>
        /// <param name="extraColumns">额外信息字段</param>
        /// <param name="dataTable">查询数据信息</param>
        /// <param name="batchSize">批插参数</param>
        public void BatchUpdate(String table, String[] keyColumns, String[] dataColumns, String[] extraColumns, DataTable dataTable, Int32 batchSize)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "BatchUpdate(table='{0}', key='{1}', data='{2}', datatable='{3}', batchsize='{4}')", 
                table, String.Join(",", keyColumns), String.Join(";", dataColumns), dataTable.TableName, batchSize);

            dataTable.ExtendedProperties["InsertCommand"] = GenerateInsertCommand(null, table + TablePrefix, keyColumns, dataColumns, extraColumns).CommandText;
            dataTable.ExtendedProperties["ConnectionString"] = GetConnectionString("ODS.Oracle");

            SaveDataToDB(dataTable);
        }

        /// <summary>
        /// 将查询信息批量插入查询临时表
        /// 查询临时表规划:    查询表/查询视图<随机编号1~999>
        /// </summary>
        /// <param name="table">查询的数据表</param>
        /// <param name="keyColumns">维度字段</param>
        /// <param name="dataColumns">数据字段</param>
        /// <param name="extraColumns">额外信息字段</param>
        /// <param name="dataTable">查询数据信息</param>
        /// <param name="batchSize">批插参数</param>
        public void BatchQuery(String table, String[] keyColumns, String[] dataColumns, string[] extraColumns, DataTable dataTable, Int32 batchSize)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "BatchQuery(table='{0}', key='{1}', data='{2}', extra='{3}', datatable='{4}', batchsize='{5}')",
                   table, String.Join(",", keyColumns), String.Join(";", dataColumns), String.Join(";", extraColumns), dataTable.TableName, batchSize);

            dataTable.ExtendedProperties["InsertCommand"] = GenerateInsertCommand(null, table + TablePrefix, keyColumns, new string[] { }, extraColumns).CommandText;
            dataTable.ExtendedProperties["ConnectionString"] = GetConnectionString("ODS.Oracle");
            SaveDataToDB(dataTable);

        }

        /// <summary>
        /// 检查维度信息
        /// </summary>
        /// <param name="path"></param>
        /// <param name="tablecategory"></param>
        /// <param name="keydimensions"></param>
        /// <param name="datadimensions"></param>
        /// <param name="mode"></param>
        public void CheckDimension(string path, string tablecategory, string[] keydimensions, string[] datadimensions, string mode)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "CheckDimension(path='{0}', category='{1}', keydimensions='{2}', datadimensions='{3}', mode='{4}')", path, tablecategory, String.Join(",", keydimensions), string.Join(",", datadimensions), mode);

            MetaDataSettings settings = MetaDataSettings.Load(path);
            foreach (MetaTable table in settings.MetaTables)
            {
                if (table.Name.ToUpper() == tablecategory.ToUpper())
                {
                    StringBuilder sb = new StringBuilder();
                    List<string> dimensions = new List<string>(keydimensions);
                    dimensions.AddRange(datadimensions);

                    for (int index = 0; index < dimensions.Count; ++index)
                    {
                        dimensions[index] = dimensions[index].ToUpper();
                    }

                    foreach (MetaFiled field in table.MetaFileds)
                    {
                        if (dimensions.IndexOf(field.Name.ToUpper()) < 0)
                        {
                            sb.Append(String.Format("Missing Dimension: {0}\n", field.Name.ToUpper()));
                        }
                        else
                        {
                            dimensions.RemoveAt(dimensions.IndexOf(field.Name.ToUpper()));
                        }
                    }

                    foreach (string name in dimensions)
                    {
                        sb.Append(String.Format("Unnecessary Dimension: {0}\n", name));
                    }

                    if (sb.ToString().Trim() != "")
                    {
                        throw new WebServiceException(WebServiceErrorCode.Error_Retrieve_Failture, sb.ToString());
                    }
                }
            }
        }

        /// <summary>
        /// 检查元数据
        /// </summary>
        /// <param name="path"></param>
        /// <param name="tablecategory"></param>
        /// <param name="datatable"></param>
        /// <param name="prefix"></param>
        /// <param name="mode"></param>
        public void CheckMetaData(string path, string tablecategory, string datatable, string prefix, string mode)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "CheckMetaData(path='{0}', category='{1}', datatable='{2}', prefix='{3}', mode='{4}')", path, tablecategory, datatable, prefix, mode);

            MetaDataSettings settings = MetaDataSettings.Load(path);
            foreach (MetaTable table in settings.MetaTables)
            {
                if (table.Name.ToUpper() == tablecategory.ToUpper())
                {
                    Logging.WriteLog(LoggingLevel.INFO, "Found matched metatable settings, metafileds count={0}.", table.MetaFileds.Count);
                    StringBuilder sb = new StringBuilder();

                    foreach (MetaFiled field in table.MetaFileds)
                    {
                        if (field.MetaDataTable.Trim() == "")
                            continue;

                        Logging.WriteLog(LoggingLevel.DEBUG, "CheckMetaData(sPrefix='{0}', sDataTable='{1}', sDataField='{2}', sMetaTable='{3}', sMetaField='{4}')", 
                            prefix, datatable, field.Name, field.MetaDataTable, field.MetaDataFiled);

                        OracleCommand objCmd = new OracleCommand();
                        objCmd.Connection = objConnection;
                        objCmd.CommandText = String.Format("{0}.CheckMetaData", PackageName);
                        objCmd.CommandType = CommandType.StoredProcedure;
                        objCmd.Parameters.Add("sPrefix", OracleDbType.Varchar2, 40).Value = prefix;
                        objCmd.Parameters.Add("sDataTable", OracleDbType.Varchar2, 40).Value = datatable;
                        objCmd.Parameters.Add("sDataField", OracleDbType.Varchar2, 40).Value = field.Name;
                        objCmd.Parameters.Add("sMetaTable", OracleDbType.Varchar2, 40).Value = field.MetaDataTable;
                        objCmd.Parameters.Add("sMetaField", OracleDbType.Varchar2, 40).Value = field.MetaDataFiled;
                        objCmd.Parameters.Add("sFilter", OracleDbType.Varchar2, 200).Value = field.Filter;
                        objCmd.Parameters.Add("sMode", OracleDbType.Varchar2, 40).Value = mode;
                        objCmd.Parameters.Add("cResults", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

                        OracleDataReader reader = objCmd.ExecuteReader();
                        
                        while (reader.Read())
                        {
                            sb.Append(String.Format("Postion (Row {0}, Column {1}) Member {2} '{3}' {4}\n", reader["RowIDX"], reader["ColumnIDX"], field.Name, reader["MemberName"], reader["Detail"]));
                        }

                        reader.Close();
                    }

                    if (sb.ToString().Trim().Length > 0)
                    {
                        Logging.WriteLog(LoggingLevel.INFO, "Meta Filed Test Failed, {0}.", sb.ToString());
                        throw new WebServiceException(WebServiceErrorCode.Error_Retrieve_Failture, sb.ToString());
                    }
                }
            }
        }

        /// <summary>
        /// 加载数据到目标系统
        /// </summary>
        /// <param name="system">目标系统 (如: HFM)</param>
        /// <param name="entity">实体</param>
        /// <param name="scenario">场景</param>
        /// <param name="year">年</param>
        /// <param name="period">月份</param>
        public void LoadData(String system, string entity, string scenario, string year, string period)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "LoadData(System={0}, Entity={1}, Scenario={2}, Year={3}, Period={4})", system, entity, scenario, year, period);

            objCommand = new OracleCommand();

            objCommand.Connection = objConnection;
            objCommand.CommandText = String.Format("{0}.LoadData", PackageName);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.Add("sSystem", OracleDbType.Varchar2, 40).Value = system;
            objCommand.Parameters.Add("sEntity", OracleDbType.Varchar2, 40).Value = entity;
            objCommand.Parameters.Add("sScenario", OracleDbType.Varchar2, 40).Value = scenario;
            objCommand.Parameters.Add("sYear", OracleDbType.Varchar2, 40).Value = year;
            objCommand.Parameters.Add("sPeriod", OracleDbType.Varchar2, 40).Value = period;

            objCommand.ExecuteNonQuery();

            return;
        }

        public Boolean CheckEntity(String Category, String entity)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "CheckEntity(Category={0}, Entity={1})", Category, entity);

            objCommand = new OracleCommand();

            objCommand.Connection = objConnection;
            objCommand.CommandText = ConfigurationManager.AppSettings[String.Format ("ODS:CheckEntity:{0}:SQL", Category)];
            objCommand.Parameters.Add("Entity", OracleDbType.Varchar2, 40).Value = entity;

            OracleDataReader reader = objCommand.ExecuteReader();
            if (reader.Read())
            {
                return Boolean.Parse(reader["result"].ToString());
            }
            else
            {
                return false;
            }
            
        }


        public StringBuilder LoadSQL(string sql, string spliter, string scenario, string entity, string year, string period)
        {
            StringBuilder buf = new StringBuilder();

            objCommand = new OracleCommand();
            objCommand.Connection = objConnection;
            objCommand.CommandText = sql;
            objCommand.Parameters.Add("Scenario", OracleDbType.Varchar2, 40).Value = scenario;
            objCommand.Parameters.Add("Entity", OracleDbType.Varchar2, 40).Value = entity;
            objCommand.Parameters.Add("Year", OracleDbType.Varchar2, 40).Value = year;
            objCommand.Parameters.Add("Period", OracleDbType.Varchar2, 40).Value = period;
            OracleDataReader reader = objCommand.ExecuteReader();

            while (reader.Read())
            {
                for (int index = 0; index < reader.FieldCount; ++index)
                {
                    if (index != 0)
                    {
                        buf.Append(";");
                    } 
                    buf.Append(reader[index]);
                }
                buf.Append("\r\n");
            }

            return buf;
        }


        /// <summary>
        /// 生成批插命令
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="table">目标数据表</param>
        /// <param name="keyColumns">维度字段</param>
        /// <param name="dataColumns">数据字段</param>
        /// <param name="extraColumns">额外数据字段</param>
        /// <returns></returns>
        private OracleCommand GenerateInsertCommand(OracleConnection connection, string table, string[] keyColumns, string[] dataColumns, string[] extraColumns)
        {
            objCommand = new OracleCommand();
            StringBuilder sb = new StringBuilder();

            sb.Append("!");
            foreach (string key in dataColumns)
            {
                sb.Append(String.Format(", :{0} ", key));
                objCommand.Parameters.Add(String.Format(":{0}", key), OracleDbType.Varchar2, 20, key);
            }
            foreach (string key in keyColumns)
            {
                sb.Append(String.Format(", :{0}", key));
                objCommand.Parameters.Add(String.Format(":{0}", key), OracleDbType.Varchar2, 20, key);
            }
            foreach (string key in extraColumns)
            {
                if (key.Trim() != "")
                {
                    sb.Append(String.Format(", :{0}", key));
                    objCommand.Parameters.Add(String.Format(":{0}", key), OracleDbType.Varchar2, 20, key);
                }
            }
            sb = sb.Replace("!,", "");

            objCommand.CommandText = String.Format("INSERT INTO {0} ({2}) VALUES ({1})", table, sb.ToString(), sb.Replace(":", "").ToString());
            Logging.WriteLog(LoggingLevel.DEBUG, "Insert Command = {0}", objCommand.CommandText);
            objCommand.Connection = connection;
            objCommand.UpdatedRowSource = UpdateRowSource.None;
            
            return objCommand;
        }


        public void SaveDataToDB(DataTable table)
        {
            OracleConnection objConnection = null;
            try
            {
                objConnection = new OracleConnection();
                objConnection.ConnectionString = table.ExtendedProperties["ConnectionString"].ToString();
                objConnection.Open();

                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = table.ExtendedProperties["InsertCommand"].ToString();
                cmd.Connection = objConnection;
                cmd.ArrayBindCount = table.Rows.Count;

                Regex regex = new Regex(@":(\w+)");
                MatchCollection matches = regex.Matches(cmd.CommandText);
                foreach (Match match in matches)
                {
                    DataColumn column = table.Columns[match.Groups[1].Value];

                    OracleParameter objParameter = null;
                    if (column.DataType == System.Type.GetType("System.Double"))
                    {
                        objParameter = new OracleParameter(column.ColumnName, OracleDbType.Double);

                        Double[] records = new Double[table.Rows.Count];
                        for (int index = 0; index < records.Length; ++index)
                        {
                            records[index] = Double.Parse(table.Rows[index][column.ColumnName].ToString());
                        }
                        objParameter.Value = records;
                    }
                    else
                    {
                        objParameter = new OracleParameter(column.ColumnName, OracleDbType.Varchar2, 40);

                        string[] records = new string[table.Rows.Count];
                        for (int index = 0; index < records.Length; ++index)
                        {
                            records[index] = table.Rows[index][column.ColumnName].ToString();
                        }
                        objParameter.Value = records;
                    }

                    objParameter.Direction = ParameterDirection.Input;
                    cmd.Parameters.Add(objParameter);
                }

                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (objConnection != null)
                {
                    objConnection.Close();
                    objConnection.Dispose();
                }
            }
        }

        /// <summary>  
        /// 处理DataRow筛选条件的特殊字符  
        /// </summary>  
        /// <param name="rowFilter">行筛选条件表达式</param>  
        /// <returns></returns>  
        public static string DvRowFilter(string rowFilter)
        {

            //在DataView的RowFilter里面的特殊字符要用"[]"括起来，单引号要换成"''",他的表达式里面没有通配符的说法  
            return rowFilter.Replace("\'", "''");
        } 

    }
}